package com.daxia.bumall.web;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.sql.DataSource;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import com.daxia.bumall.common.Logs;
import com.daxia.bumall.dto.FactoryInfoDTO;
import com.daxia.bumall.dto.ProductDTO;
import com.daxia.bumall.service.SystemLogService;
import com.daxia.bumall.support.Page;
import com.daxia.bumall.web.controller.BaseController;

@Controller
@RequestMapping(value = "/dev", produces="text/html;charset=UTF-8")
public class DevController extends BaseController {
    @Autowired
    private DataSource dataSource;
    @Autowired
    private SystemLogService systemLogService;
    
	@RequestMapping(value = "")
    public String _index() throws Exception {
        return "dev/dev_index";
    }
	
	 
	@RequestMapping(value = "updateCodes")
	public String updateCodes() throws Exception {
		Page page = new Page();
		page.setPageSize(200);
		int p = 1;
		int count = 0;
		while (true) {
			page.setPage(p ++);
			ProductDTO query = new ProductDTO();
			query.setFromAdmin(true);
			List<ProductDTO> products = productService.find(query, page);
			if (CollectionUtils.isEmpty(products)) {
				break;
			}
			for (ProductDTO productDTO : products) {
				count ++;
				Logs.COMMON.debug("finished " + count);
				if (StringUtils.isNotBlank(productDTO.getCodes())) {
					continue;
				}
				eventDispather.specificationChanged(productDTO.getId());
			}
		}
		return "dev/dev_index";
	}
	
	@ResponseBody
	@RequestMapping(value = "deleteProduct")
	public String deleteProduct(Long id) throws Exception {
		productService.deleteByIdPhisycal(id);
		return ok();
	}
	
	@Transactional
	@ResponseBody
	@RequestMapping(value = "deleteProductByFactoryId")
	public String deleteProductByFactoryId(Long id) throws Exception {
		FactoryInfoDTO f = factoryInfoService.load(id);
		productService.deleteByUserId(f.getUser().getId());
		productService.cleanup();
		return ok();
	}

	@Transactional
	@ResponseBody
	@RequestMapping(value = "deleteProductByUserId")
	public String deleteProductByUserId(Long id) throws Exception {
		productService.deleteByUserId(id);
		productService.cleanup();
		return ok();
	}
	
	@Transactional
	@ResponseBody
	@RequestMapping(value = "cleanupProduct")
	public String cleanupProduct() throws Exception {
		productService.cleanup();
		return ok();
	}

	
    @RequestMapping(value = "login", method = RequestMethod.GET)
    public String login() throws Exception {
        return "dev/dev_login";
    }
    
    @PreAuthorize(value = "superadmin")
    @RequestMapping(value = "query", method = RequestMethod.GET)
    public String query() throws Exception {
        return "dev/dev_query";
    }
    
    @RequestMapping(value = "execute", method = RequestMethod.POST)
    public String execute(String sql, Map<String, Object> map) throws Exception {
    	map.put("sql", sql);
    	 long start = System.currentTimeMillis();
         try {
             if (sql.trim().toLowerCase().startsWith("select")) {
                 return doQuery(sql, map);
             } else {
                 return doExecute(sql, map);
             }
         } finally {
             long cost = System.currentTimeMillis() - start;
             map.put("cost", cost);
         }
    }
         
    
    private String doQuery(String sql, Map<String, Object> map) throws SQLException {
        Connection connection = dataSource.getConnection();
        
        ResultSet rs = connection.createStatement().executeQuery(sql);
        ResultSetMetaData rsmd = rs.getMetaData();
        List<String> columnNames = new ArrayList<String>();
        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
            columnNames.add(rsmd.getColumnName(i));
        }
        List<List<Object>> columnValues = new ArrayList<List<Object>>();
        while (rs.next()) {
            List<Object> rowValues = new ArrayList<Object>();
            for (String columnName : columnNames) {
                rowValues.add(rs.getObject(columnName));
            }
            columnValues.add(rowValues);
        }
        connection.close();
        
        map.put("columnNames", columnNames);
        map.put("columnValues", columnValues);
        return "dev/dev_query";
    }
    
    private String doExecute(String sql, Map<String, Object> map) throws SQLException {

        Connection connection = dataSource.getConnection();
        String[] sqls = sql.split(";");
        try {
        	connection.setAutoCommit(false);
	        for (String string : sqls) {
	        	int result = connection.createStatement().executeUpdate(string.trim());
	        	map.put("result", result);
			}
	        connection.commit();
        } catch (Exception e) {
        	connection.rollback();
        } finally {
        	connection.setAutoCommit(true);
        	
        }
        connection.close();
        
        return "dev/dev_query";
    
    }

    private String doQuery(String sql, Map<String, Object> map, Integer page, Integer pageSize) throws SQLException {
        if (page == null) {
            page = 1;
        }
        if (pageSize == null) {
            pageSize = 100;
        }
        
        Connection connection = dataSource.getConnection();
        
        ResultSet rs = connection.createStatement().executeQuery(sql);
        ResultSetMetaData rsmd = rs.getMetaData();
        List<String> columnNames = new ArrayList<String>();
        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
            columnNames.add(rsmd.getColumnName(i));
        }
        List<List<Object>> columnValues = new ArrayList<List<Object>>();
        while (rs.next()) {
            List<Object> rowValues = new ArrayList<Object>();
            for (String columnName : columnNames) {
                rowValues.add(rs.getObject(columnName));
            }
            columnValues.add(rowValues);
        }
        connection.close();
        
        map.put("columnNames", columnNames);
        map.put("columnValues", columnValues);
        return "dxtools/db/dxtools_db_result_query";
    }
    
    @RequestMapping(value = "login", method = RequestMethod.POST)
    public String loginSubmit(String username, String password, HttpServletRequest request) throws Exception {
        if ("daxia".equals(username) && "daxia".equals(password)) {
            request.getSession().setAttribute("currentDev", "kevin");
            return "redirect:" + getCtx() + "/dev/index";
        } else {
            request.setAttribute("msg", "用户名或密码错误");
            return "dev/dev_login";
        }
    }
    
    @ResponseBody
	@RequestMapping("fixProfit")
	public String fixProfit() throws Exception {
		profitService.fixProfit();
		return ok();
	}
}
